package com.verisette.ncos.persistence.dao;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import org.corp.agi.common.bean.PagingBean;
import org.corp.agi.persistence.dao.CommonDAOImpl;
import com.verisette.ncos.persistence.domain.CustomMasterUserProfile;
import com.verisette.ncos.persistence.domain.CustomUserAndGroup;
import com.verisette.ncos.persistence.domain.MasterUserGroupToUserProfile;
import com.verisette.ncos.persistence.domain.MasterUserProfile;

public class UserProfileManagementDAOImpl extends CommonDAOImpl implements UserProfileManagementDAO {
	private Logger logger = Logger.getLogger(UserProfileManagementDAOImpl.class);
	@Override
	public List<CustomMasterUserProfile> inquiryUserProfile(MasterUserProfile userProfile, PagingBean pagingBean){
		if(logger.isInfoEnabled()) logger.info( this.getClass().getSimpleName() + ": inquiryUserProfile" );
		List param = new LinkedList();
		List<CustomMasterUserProfile> customMasterProfileList = null;
		StringBuffer q1 = new StringBuffer();
		
		q1.append("SELECT  MASTER_USER_PROFILE.USP_ID, MASTER_USER_PROFILE.USP_PASSWORD, MASTER_USER_USG_TO_USP.UGP_ID, MASTER_USER_GROUP.USG_ID, MASTER_USER_PROFILE.USP_USER_NAME, MASTER_USER_PROFILE.USP_TITLE, MASTER_USER_PROFILE.USP_FNAME, MASTER_USER_PROFILE.USP_MNAME,");
		q1.append(" MASTER_USER_PROFILE.USP_LNAME, MASTER_USER_GROUP.USG_NAME, MASTER_USER_PROFILE.USP_STATUS");
		q1.append(" FROM MASTER_USER_GROUP");
		q1.append(" INNER JOIN MASTER_USER_USG_TO_USP ON MASTER_USER_GROUP.USG_ID = MASTER_USER_USG_TO_USP.USG_ID INNER JOIN MASTER_USER_PROFILE");
		q1.append(" ON MASTER_USER_USG_TO_USP.USP_ID = MASTER_USER_PROFILE.USP_ID AND MASTER_USER_USG_TO_USP.UGP_STATUS = 'A'");
		
		if(userProfile != null && userProfile.getUspUserName() != null && !"".equals(userProfile.getUspUserName())){
			q1.append(" WHERE MASTER_USER_PROFILE.USP_USER_NAME like ?");
			param.add(userProfile.getUspUserName());
		}
		
		q1.append(" order by  MASTER_USER_PROFILE.USP_ID, ");
		
		customMasterProfileList = (List<CustomMasterUserProfile>)nativeQuery(q1.toString(), CustomMasterUserProfile.class, pagingBean, param.toArray());

		return customMasterProfileList;
		
	}
	
	public List<CustomMasterUserProfile> inquiryUserProfileMergeRole(MasterUserProfile userProfile, PagingBean pagingBean){
		if(logger.isInfoEnabled()) logger.info( this.getClass().getSimpleName() + ": inquiryUserProfileMergeRole" );
		List<Object> param = new LinkedList<Object>();
		StringBuilder q1 = new StringBuilder();
		q1.append("SELECT DISTINCT MASTER_USER_PROFILE.*, ");
		q1.append(" 0 as UGP_ID, '' as USG_NAME, 0 as USG_ID"); //by pass UGP_ID, USG_ID and USG_NAME 
		q1.append(" FROM MASTER_USER_PROFILE "); 
		q1.append(" INNER JOIN MASTER_USER_USG_TO_USP ON MASTER_USER_USG_TO_USP.USP_ID = MASTER_USER_PROFILE.USP_ID  AND MASTER_USER_USG_TO_USP.UGP_STATUS = 'A' ");  
		q1.append(" INNER JOIN MASTER_USER_GROUP ON MASTER_USER_GROUP.USG_ID = MASTER_USER_USG_TO_USP.USG_ID ");
		q1.append("WHERE 1 = 1 ");
		if (StringUtils.isNotBlank(userProfile.getUsername())) {
			q1.append("AND MASTER_USER_PROFILE.USP_USER_NAME LIKE ? ");
			param.add("%"+userProfile.getUsername()+"%");
		}
		if (StringUtils.isNotBlank(userProfile.getUserGroup())) {
			q1.append("AND MASTER_USER_GROUP.USG_ID = ? ");
			param.add(userProfile.getUserGroup());
		}
		if("uspId".equals(pagingBean.getOrderBy())){
			q1.append(" order by  MASTER_USER_PROFILE.USP_ID ");
			q1.append(pagingBean.getOrderMode());
			
		}else if("uspUserName".equals(pagingBean.getOrderBy())){
			q1.append(" order by  MASTER_USER_PROFILE.USP_USER_NAME ");
			q1.append(pagingBean.getOrderMode());
			
		}else if("uspFname".equals(pagingBean.getOrderBy())){
			q1.append(" order by  MASTER_USER_PROFILE.USP_FNAME ");
			q1.append(pagingBean.getOrderMode());
			
		//}else if("usgName".equals(pagingBean.getOrderBy())){ //Can't sort user group
		//	q1.append(" order by  MASTER_USER_GROUP.USG_NAME ");
		//	q1.append(pagingBean.getOrderMode());
			
		}else{
			q1.append(" order by  MASTER_USER_PROFILE.USP_ID ");
			q1.append(pagingBean.getOrderMode());
		}
		
		List<CustomMasterUserProfile> userProfileList = (List<CustomMasterUserProfile>)nativeQuery(q1.toString(), CustomMasterUserProfile.class, pagingBean, param.toArray());
		
		if (userProfileList.size()>0) {
			StringBuilder q2 = new StringBuilder();
			q2.append("SELECT MASTER_USER_GROUP.USG_NAME, MASTER_USER_PROFILE.USP_ID "); 
			q2.append("FROM MASTER_USER_PROFILE "); 
			q2.append(" INNER JOIN MASTER_USER_USG_TO_USP ON MASTER_USER_USG_TO_USP.USP_ID = MASTER_USER_PROFILE.USP_ID  AND MASTER_USER_USG_TO_USP.UGP_STATUS = 'A' ");  
			q2.append(" INNER JOIN MASTER_USER_GROUP ON MASTER_USER_GROUP.USG_ID = MASTER_USER_USG_TO_USP.USG_ID ");
			q2.append("WHERE MASTER_USER_PROFILE.USP_ID IN ( ");
			for(int i = 0; i < userProfileList.size(); i++){
				q2.append(userProfileList.get(i).getUspId()+",");	
			}
			//remove last comma
			q2.setCharAt(q2.length()-1, ')');
			q2.append("ORDER BY MASTER_USER_GROUP.USG_NAME, MASTER_USER_PROFILE.USP_ID ");
			
			List<CustomUserAndGroup> customUserRole = (List<CustomUserAndGroup>)nativeQuery(q2.toString(), CustomUserAndGroup.class);
			for (int j = 0; j < userProfileList.size(); j++){
				for (int i = 0; i < customUserRole.size(); i++){
					if (customUserRole.get(i).getUspId().equals(userProfileList.get(j).getUspId())) {
						String group = userProfileList.get(j).getGroup();
						if (StringUtils.isBlank(group)) {
							userProfileList.get(j).setGroup(((CustomUserAndGroup) customUserRole.get(i)).getUsgName());
						} else {
							group += ", " + ((CustomUserAndGroup) customUserRole.get(i)).getUsgName();
							userProfileList.get(j).setGroup(group);
							continue;
						}
					}
				}
			}
		}
		return userProfileList;	
	}
	
	@Deprecated
	public List<CustomMasterUserProfile> inquiryUserProfileMergeRoleOld(MasterUserProfile userProfile, PagingBean pagingBean){
		if(logger.isInfoEnabled()) logger.info( this.getClass().getSimpleName() + ": inquiryUserProfileMergeRole" );
		List<MasterUserProfile> totalUserPerRowList = null;
		
		if(userProfile != null && userProfile.getUspUserName() != null && !"".equals(userProfile.getUspUserName())){
			// With condition
			//****Edit by Sudarat Trairatanobhas(Ming) 11/05/2010
				//****Shouldn't have paging on this
			//totalUserPerRowList = (List<MasterUserProfile>)findByExampleLike(userProfile);
			//****Comment by Sudarat Trairatanobhas(Ming) 11/05/2010
			totalUserPerRowList = (List<MasterUserProfile>)findByExampleLike(userProfile, pagingBean);
			logger.debug("With condition");
			
		}else{
			// Without condition
			//****Edit by Sudarat Trairatanobhas(Ming) 11/05/2010
				//****Shouldn't have paging on this
			//totalUserPerRowList = (List<MasterUserProfile>)findAll(MasterUserProfile.class);
			//****Comment by Sudarat Trairatanobhas(Ming) 11/05/2010
			totalUserPerRowList = (List<MasterUserProfile>)findAll(MasterUserProfile.class, pagingBean);
			logger.debug("Without condition");
		}
		
		List param = new LinkedList();
		List<CustomMasterUserProfile> customMasterProfileList = null;
		List<CustomMasterUserProfile> userProfileMergeRoleList = new ArrayList<CustomMasterUserProfile>();
		CustomMasterUserProfile preUser = null;
		StringBuffer q1 = new StringBuffer();
		
		if(totalUserPerRowList.size() == 0){
			// Not Found User and roles
			return userProfileMergeRoleList;
			
		}
		for(MasterUserProfile object:totalUserPerRowList){
			logger.debug(object.getUspUserName());
		}
		
		q1.append("SELECT  MASTER_USER_PROFILE.USP_ID, MASTER_USER_PROFILE.USP_PASSWORD, MASTER_USER_USG_TO_USP.UGP_ID, MASTER_USER_GROUP.USG_ID, MASTER_USER_PROFILE.USP_USER_NAME, MASTER_USER_PROFILE.USP_TITLE, MASTER_USER_PROFILE.USP_FNAME, MASTER_USER_PROFILE.USP_MNAME,");
		q1.append(" MASTER_USER_PROFILE.USP_LNAME, MASTER_USER_GROUP.USG_NAME, MASTER_USER_PROFILE.USP_STATUS, MASTER_USER_PROFILE.USP_TEL_NO, MASTER_USER_PROFILE.USP_MOBILE_NO,");
		q1.append(" MASTER_USER_PROFILE.USP_EMAIL, MASTER_USER_PROFILE.USP_EXPIRE_DATE, MASTER_USER_PROFILE.USP_LAST_ACCESS_DATE, MASTER_USER_PROFILE.USP_WRONG_LOGIN_COUNTER, MASTER_USER_PROFILE.USP_LOCK_FLAG, MASTER_USER_PROFILE.USP_FORCE_CHNG_PWD_FLAG");
		q1.append(" FROM MASTER_USER_GROUP");
		q1.append(" INNER JOIN MASTER_USER_USG_TO_USP ON MASTER_USER_GROUP.USG_ID = MASTER_USER_USG_TO_USP.USG_ID INNER JOIN MASTER_USER_PROFILE");
		q1.append(" ON MASTER_USER_USG_TO_USP.USP_ID = MASTER_USER_PROFILE.USP_ID AND MASTER_USER_USG_TO_USP.UGP_STATUS = 'A'");
		q1.append(" WHERE MASTER_USER_PROFILE.USP_USER_NAME in (");
		
		for(int i = 0; i < totalUserPerRowList.size(); i++){
			q1.append("'" + StringEscapeUtils.escapeSql(totalUserPerRowList.get(i).getUspUserName()) + "'" + ",");
			
		}

		q1.setCharAt(q1.length()-1, ')');
		
		// Add by thippawan @2010-09-22
		if( (null!=userProfile.getUserGroup())&&(!(userProfile.getUserGroup().equals(""))) ){
			q1.append(" AND MASTER_USER_USG_TO_USP.USG_ID = ?");
			param.add(userProfile.getUserGroup());
		}
		
//		q1.append(" order by  MASTER_USER_PROFILE.USP_ID ");
		if("uspId".equals(pagingBean.getOrderBy())){
			q1.append(" order by  MASTER_USER_PROFILE.USP_ID ");
			q1.append(pagingBean.getOrderMode());
			
		}else if("uspUserName".equals(pagingBean.getOrderBy())){
			q1.append(" order by  MASTER_USER_PROFILE.USP_USER_NAME ");
			q1.append(pagingBean.getOrderMode());
			
		}else if("uspFname".equals(pagingBean.getOrderBy())){
			q1.append(" order by  MASTER_USER_PROFILE.USP_FNAME ");
			q1.append(pagingBean.getOrderMode());
			
		}else if("usgName".equals(pagingBean.getOrderBy())){
			q1.append(" order by  MASTER_USER_GROUP.USG_NAME ");
			q1.append(pagingBean.getOrderMode());
			
		}else{
			q1.append(" order by  MASTER_USER_PROFILE.USP_ID ");
			q1.append(pagingBean.getOrderMode());
		}
		
		customMasterProfileList = (List<CustomMasterUserProfile>)nativeQuery(q1.toString(), CustomMasterUserProfile.class, param.toArray()); // 09/11/2010 Mix: Move pagingBean to first query 
		
		for(int i = 0; i < customMasterProfileList.size(); i++){
		
			if(i == 0){
				preUser = new CustomMasterUserProfile();
				cloneValueFromCustomMasterUserProfile(customMasterProfileList.get(i), preUser);
				
				if(i == customMasterProfileList.size()-1)
					userProfileMergeRoleList.add(preUser);
				
			}else{
				if(preUser.getUspId().intValue() == customMasterProfileList.get(i).getUspId().intValue()){
					preUser.setUsgName(preUser.getUsgName() + ", " + customMasterProfileList.get(i).getUsgName());
					
					if(i == customMasterProfileList.size()-1)
						userProfileMergeRoleList.add(preUser);
					
				}else{
					userProfileMergeRoleList.add(preUser);
					preUser = new CustomMasterUserProfile();
					cloneValueFromCustomMasterUserProfile(customMasterProfileList.get(i), preUser);
					
					if(i == customMasterProfileList.size()-1)
						userProfileMergeRoleList.add(customMasterProfileList.get(i));
					
				}
				
			}
			
		}
		
//Comment by thippawan @20100907 [wrong render condition row per page]
//		pagingBean.setTotalRows(userProfileMergeRoleList.size()); // Protect from data added by manual case
		
		return userProfileMergeRoleList;
		
	}
	
	@Override
	public List<CustomMasterUserProfile> viewUserProfile(MasterUserProfile userProfile) {
		List param = new LinkedList();
		List<CustomMasterUserProfile> customMasterProfileList = null;
		StringBuffer q1 = new StringBuffer();
		
		q1.append("SELECT  MASTER_USER_PROFILE.USP_ID, MASTER_USER_PROFILE.USP_PASSWORD, MASTER_USER_USG_TO_USP.UGP_ID, MASTER_USER_GROUP.USG_ID, MASTER_USER_PROFILE.USP_USER_NAME, MASTER_USER_PROFILE.USP_TITLE, MASTER_USER_PROFILE.USP_FNAME, MASTER_USER_PROFILE.USP_MNAME,");
		q1.append(" MASTER_USER_PROFILE.USP_LNAME, MASTER_USER_GROUP.USG_NAME, MASTER_USER_PROFILE.USP_STATUS, MASTER_USER_PROFILE.USP_TEL_NO, MASTER_USER_PROFILE.USP_MOBILE_NO,");
		q1.append(" MASTER_USER_PROFILE.USP_EMAIL, MASTER_USER_PROFILE.USP_EXPIRE_DATE, MASTER_USER_PROFILE.USP_LAST_ACCESS_DATE, MASTER_USER_PROFILE.USP_WRONG_LOGIN_COUNTER, MASTER_USER_PROFILE.USP_LOCK_FLAG, MASTER_USER_PROFILE.USP_FORCE_CHNG_PWD_FLAG");
		q1.append(" FROM MASTER_USER_GROUP");
		q1.append(" INNER JOIN MASTER_USER_USG_TO_USP ON MASTER_USER_GROUP.USG_ID = MASTER_USER_USG_TO_USP.USG_ID INNER JOIN MASTER_USER_PROFILE");
		q1.append(" ON MASTER_USER_USG_TO_USP.USP_ID = MASTER_USER_PROFILE.USP_ID");
		q1.append(" WHERE MASTER_USER_PROFILE.USP_ID like ? AND MASTER_USER_USG_TO_USP.UGP_STATUS = 'A'");
		
		param.add(userProfile.getUspId());
		
		customMasterProfileList = (List<CustomMasterUserProfile>)nativeQuery(q1.toString(), CustomMasterUserProfile.class, param.toArray());
		
		return customMasterProfileList;
	}

	@Override
	public boolean findAssignedRole(int uspId, String usgId) {
		List param = new LinkedList();
		List<CustomMasterUserProfile> customMasterProfileList = null;
		StringBuffer q1 = new StringBuffer();
		boolean foundRecord = false;
		
		q1.append("SELECT * FROM MASTER_USER_USG_TO_USP WHERE USP_ID like ? AND USG_ID like ?");
		
		param.add(uspId);
		param.add(usgId);
		
		MasterUserGroupToUserProfile usgToUsp = (MasterUserGroupToUserProfile)nativeQuery(q1.toString(), MasterUserGroupToUserProfile.class, param.toArray());
		
		if(usgToUsp != null)
			foundRecord = true;
		
		return foundRecord;
	}
	
	@Override
	public void updateUserProfile(MasterUserProfile userProfile, String updateBy, Date currentDate) {
		List param = new LinkedList();
		StringBuffer q1 = new StringBuffer();
		
		q1.append("UPDATE MASTER_USER_PROFILE");
		//q1.append(" SET USP_USER_NAME = ?, USP_PASSWORD = ?, USP_TITLE = ?, USP_FNAME = ?, USP_MNAME = ?, USP_LNAME = ?,");
		q1.append(" SET USP_USER_NAME = ?, USP_TITLE = ?, USP_FNAME = ?, USP_MNAME = ?, USP_LNAME = ?,");
		q1.append(" USP_STATUS = ?, USP_LAST_UPDATED_DATE = ?, USP_LAST_UPDATED_BY = ?,");
		q1.append(" USP_TEL_NO = ?, USP_MOBILE_NO = ?, USP_EMAIL = ?");
		//q1.append(" USP_LOCK_FLAG = ?"); 
		q1.append(" WHERE USP_ID = ?");
		
		param.add(userProfile.getUspUserName());
		//param.add(userProfile.getUspPassword());
		param.add(userProfile.getUspTitle());
		param.add(userProfile.getUspFname());
		param.add(userProfile.getUspMname());
		param.add(userProfile.getUspLname());
		param.add(userProfile.getUspStatus());
		param.add(new Timestamp(currentDate.getTime())); 
		param.add(updateBy);
		
		param.add(userProfile.getUspTelNo());
		param.add(userProfile.getUspMobileNo());
		param.add(userProfile.getUspEmail());
		//param.add(userProfile.getUspLockFlag());		
		param.add(userProfile.getUspId());
		
		executeNativeSQL(q1.toString(), param.toArray());
		
	}
	@Deprecated
	private void cloneValueFromCustomMasterUserProfile(CustomMasterUserProfile source, CustomMasterUserProfile destination){
		destination.setTitle(source.getTitle());
		destination.setUgpId(source.getUgpId());
		destination.setUspStatus(source.getUspStatus());
		destination.setUsgId(source.getUsgId());
		destination.setUsgName(source.getUsgName());
		destination.setUspFname(source.getUspFname());
		destination.setUspId(source.getUspId());
		destination.setUspLname(source.getUspLname());
		destination.setUspMname(source.getUspMname());
		destination.setUspPassword(source.getUspPassword());
		destination.setUspUserName(source.getUspUserName());
		
	}

	@Override
	public void updateUserResetPassword(MasterUserProfile userProfile, String updateBy, Date currentDate) {
		List param = new LinkedList();
		StringBuffer q1 = new StringBuffer();
		
		q1.append("UPDATE MASTER_USER_PROFILE");
		q1.append(" SET USP_PASSWORD = ?, USP_FORCE_CHNG_PWD_FLAG = ?, USP_EXPIRE_DATE = ?,");
		q1.append(" USP_WRONG_LOGIN_COUNTER = ?, USP_LOCK_FLAG = ?, USP_LAST_UPDATED_DATE = ?, USP_LAST_UPDATED_BY = ? "); 
		q1.append(" WHERE USP_ID = ?");
		
		param.add(userProfile.getUspPassword());		
		param.add(userProfile.getUspForceChngPwdFlag());
		param.add(userProfile.getUspExpireDate());
		param.add(userProfile.getUspWrongLoginCounter());
		param.add(userProfile.getUspLockFlag());
		param.add(new Timestamp(currentDate.getTime())); 
		param.add(updateBy);
		param.add(userProfile.getUspId());
		
		executeNativeSQL(q1.toString(), param.toArray());
		
	}

	@Override
	public void updateUserLockFlag(MasterUserProfile userProfile, String updateBy, Date currentDate) {
		List param = new LinkedList();
		StringBuffer q1 = new StringBuffer();
		
		q1.append("UPDATE MASTER_USER_PROFILE");
		q1.append(" SET USP_LOCK_FLAG = ?, USP_LAST_UPDATED_DATE = ?, USP_LAST_UPDATED_BY = ?, USP_WRONG_LOGIN_COUNTER = 0 "); 
		q1.append(" WHERE USP_ID = ?");
		
		param.add(userProfile.getUspLockFlag());
		param.add(new Timestamp(currentDate.getTime())); 
		param.add(updateBy);
		param.add(userProfile.getUspId());
		
		executeNativeSQL(q1.toString(), param.toArray());
		
	}

}
